package com.ys.zero.util;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


	public class DBHelper {
		private static String url;
		private static String user;
		private static String pwd;

		
		static {
			try {
				//读入配置文件 Properties Map集合 ==》读入配置文件
				Properties props = new Properties();
				//定义输入流 ClassLoader 类加载器
				InputStream in = DBHelper.class.getClassLoader().getResourceAsStream("conn.properties");
				props.load(in);
				url = props.getProperty("url");
				user = props.getProperty("user");
				pwd = props.getProperty("pwd");
				String driver  = props.getProperty("driver");
				Class.forName(driver);	
			} catch (Exception e) {
				e.printStackTrace();
				throw new RuntimeException("配置文件读入失败",e);
			}
		}
		//读入配置文件
		public static Connection openConnection() throws Exception {
			Connection conn = DriverManager.getConnection(url, user, pwd);
					return conn;
		}
		
		
		
		
		
		/**
		 * 定义泛型方法
		 */
		
		public static <T> List<T> query(String sql,Class<T> cls,Object...params){
			System.out.println("SQL:"  + sql);
			System.out.println("参数：" + java.util.Arrays.toString(params));
			Connection conn = null;
			try {
				
				conn = openConnection();
				//获取连接
				
				PreparedStatement ps = conn.prepareStatement(sql);
				//设置参数
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i+1, params[i]);
					System.out.println("参数:"+ params[i]);
				}
				//执行语句
				ResultSet rs = ps.executeQuery();
				List<T> ret =  new ArrayList<T>();
				//元数据对象
				ResultSetMetaData rsmd = rs.getMetaData();
				//System.out.println(rs.next());
				for (; rs.next(); ) {
					//java黑科技---反射
					T t  = cls.newInstance();//创建对象 ==》new **()
					//遍历所有的字段
					for (int i = 0; i < rsmd.getColumnCount(); i++) {
						//根据列名获取类的属性对象，引入反射包
						//获取字段名
						String columnName = rsmd.getColumnName(i+1);
						//转成Java的属性名
						String feildName = toJavaName(columnName);
						
						Field f = cls.getDeclaredField(feildName);
						//开启访问全选private属性可以访问
						f.setAccessible(true);
						//注意判断类型！！！！
						Object value = null;
						if(f.getType().equals(Integer.class)) {
							value  = rs.getInt(i+1);	
						}else if (f.getType().equals(long.class)) {
							value = rs.getLong(i+1);
						}else if (f.getType().equals(String.class)) {
							value = rs.getString(i+1);
						}else if(f.getType().equals(Date.class)) {
							value = rs.getDate(1);
						}else {
							value = rs.getObject(i+1);
						}
						f.set(t, value);	
					}
					ret.add(t);
				}
				System.out.println("返回结果:"+ret);
				return ret;	
			} catch (Exception e) {
				throw new RuntimeException("sql执行失败",e);
			}finally {
				try {
					if(conn!=null)
					conn.close();
				} catch (SQLException e) {
					throw new RuntimeException("连接关闭失败",e);
				}
				
				
			}		
		}
		/**
		 * 将数据库字段的名称转为java驼峰式
		 * 例如：user_name ==》userName
		 * @param columnName
		 * @return
		 */
		
		private static String toJavaName(String columnName) {
			//先将字符串转化小写
			columnName  = columnName.toLowerCase();
			//判断是否包含下滑线
			while(columnName.contains("_")) {
			//查找下划线
				int i = columnName.indexOf("_");
				//获取下滑线后面的字符
				char c = columnName.charAt(i+1);
				//转成大写
				c = Character.toUpperCase(c);
				//替换小写字符
				columnName = columnName.substring(0,i) + c + columnName.substring(i+2);
			}
			return columnName;
		}


	
	

		/**
		 * 只查询一个记录，根据ID，返回一个对象
		 * 例如：根据主键查询select * from  category where cid = 1;
		 */
		public static <T> T queryById(String sql,Class<T> cls,Object cid) {
			System.out.println("sql:" + sql);
			System.out.println("参数" + cid);
			Connection conn = null;
			try {
				conn = openConnection();
			    PreparedStatement ps =  conn.prepareStatement(sql);
			    ps.setObject(1, cid);
			    ResultSet rs = ps.executeQuery();
			    ResultSetMetaData rsmd = ps.getMetaData();
			   if(rs.next()) {
			    	T t = cls.newInstance();
			    	for(int i =0;i<rsmd.getColumnCount();i++) {
			    		//根据列名获取类的属性对象，引入反射包
			    		//获取字段名
						String columnName = rsmd.getColumnName(i+1);
						//转成Java的属性名
						String feildName = toJavaName(columnName);
						
						Field f = cls.getDeclaredField(feildName);
						f.setAccessible(true);
						//注意判断类型！！！！
						Object value = null;
						if(f.getType().equals(Integer.class)) {
							value  = rs.getInt(i+1);	
						}else if (f.getType().equals(long.class)) {
							value = rs.getLong(i+1);
						}else if (f.getType().equals(String.class)) {
							value = rs.getString(i+1);
						}else if(f.getType().equals(Date.class)) {
							value = rs.getDate(1);
						}else {
							value = rs.getObject(i+1);
						}
						f.set(t, value);		
			    	}
			    	if(rs.next()) {
			    		throw new RuntimeException("查询到多条记录");
			    	}
			    	return t;
			    }else {
			    	throw new RuntimeException("没有查询到记录");
			    }
			} catch (Exception e) {
				throw new RuntimeException("sql执行失败",e);
			}finally {
				try {
					conn.close();
				} catch (SQLException e) {
					throw new RuntimeException("连接关闭失败",e);
				}
			}		
		}
		
		
		
		/**
		 * 只查询一个值
		 *例如：根据统计查询 select count(*)  from  category
		 *多于一条代码应该报错
		 */
		public static Object queryValue (String sql, Object...params){
			System.out.println("sql:" + sql);
			System.out.println("参数" + java.util.Arrays.toString(params));
			Connection conn = null;
			Object ret = null;
			try {
				//获取连接
				conn = openConnection();
				PreparedStatement ps = conn.prepareStatement(sql);
				System.out.println(ps);
				//设置参数
				for (int i = 0; i < params.length; i++) {
					
					ps.setObject(i+1, params[i]);
				}
				//执行语句
				ResultSet rs = ps.executeQuery();
				
				//元数据对象
				ResultSetMetaData rsmd = rs.getMetaData();
				
				if(rs.next()) {
					//遍历所有的字段
					if(rsmd.getColumnCount()==1) {
						//根据列名获取类的属性对象，引入反射包
						//获取字段名
						String columnName = rsmd.getColumnName(1);
						//转成Java的属性名
						String feildName = toJavaName(columnName);
						ret = rs.getObject(1);
					}else {
						throw new RuntimeException("查询不是单值");
					}
					if(rs.next()) {
						throw new RuntimeException("查询不是单值");
					}
				}
			} catch (Exception e) {
				throw new RuntimeException("sql执行失败",e);
			}finally {
				try {
					conn.close();
				} catch (SQLException e) {
					throw new RuntimeException("连接关闭失败",e);
				}
			}
			
			return ret;
		}
		

		
		
		
		
		
		

	
		
		

	
	/**
	 * 查询歌手数据
	 * 
	 * @param sql
	 *            sql语句
	 * @param params
	 *            可变参数数组，sql语句中的参数
	 * @return
	 * @throws Exception 
	 * 
	 * 
	 * 
	 */
	public static <T> List<T> selectList(
			String sql, 
			ResultSetMapper<T> callback, 
			Object... params)
			throws Exception {

		System.out.println("SQL：" + sql);
		System.out.println("参数：" + Arrays.toString(params));
		Connection conn = openConnection();
		try {
			// 创建语句对象
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置查询参数
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			// 执行语句
			ResultSet rs = ps.executeQuery();
			// 定义返回结果
			List<T> ret = new ArrayList<>();
			// while(rs.next());
			for (; rs.next();) {
				T t = callback.map(rs);
				ret.add(t);
			}
			return ret;
		} finally {
			conn.close();
		}
	}

	/**
	 * 回调接口， 泛型接口
	 * 
	 * @param <T>
	 */
	public static interface ResultSetMapper<T> {
		T map(ResultSet rs) throws SQLException;
	}
	/**
	 * 更新数据，删除数据
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public static int update(String sql, Object...params)throws Exception {
		System.out.println("SQL：" + sql);
		System.out.println("参数：" + Arrays.toString(params));
		Connection conn = openConnection();
		try {
			// 创建语句对象
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置查询参数
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			return ps.executeUpdate();
		} finally {
			conn.close();
		}
	}
	
	//插入用户注册信息
		public static int  insertUser(String sql ,Object...params ) throws Exception {
			System.out.println("SQL"+sql);
			System.out.println("参数" +java.util.Arrays.toString(params));
			Connection conn = null;
			
			try {
				//建立连接
				
				
				conn = openConnection();
				
				
				//创建语句
				PreparedStatement ps = conn.prepareStatement(sql);
				//传入参数
				for(int i = 0 ; i <params.length; i++) {
					ps.setObject(i + 1 , params[i]);
					//System.out.println(i + ":"+params[i]);
				}
				
				return  ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new RuntimeException("SQL执行失败", e);
			}finally {
				//关闭资源
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					throw   new RuntimeException("连接关闭失败",e);
				}
			}
			
			
		}
		/**
		 * 用于关联查询
		 * @param sql
		 * @param params
		 * @return
		 * @throws Exception
		 */
		public static List<Map<String,Object>> selectList(String sql , Object ...params) throws Exception{
			Connection conn = openConnection();
			try {
				System.out.println("SQL:"+sql);
				System.out.println("餐宿:"+Arrays.toString(params));
				PreparedStatement ps = conn.prepareStatement(sql);
				for(int i = 0;i<params.length;i++) {
					ps.setObject(i+1, params[i]);
				}
				List<Map<String,Object>>  ret = new ArrayList<>();
				ResultSet rs = ps.executeQuery();
				ResultSetMetaData md = rs.getMetaData();
				while(rs.next()) {
					Map<String,Object> row = new HashMap<String ,Object>();
					for(int i = 0; i<md.getColumnCount(); i++) {
						String columnName = md.getColumnName(i+1);
						Object value = rs.getObject(i+1);
						row.put(columnName, value);
					}
					ret.add(row);
				}
				return ret;
			
			}finally {
				conn.close();
			}
			
		}
		
		
		public Map<String, Object> selectOne(String sql, Object... params) throws Exception {
			Connection conn = openConnection();
			try {
				System.out.println("SQL: " + sql);
				System.out.println("参数：" + Arrays.toString(params));
				PreparedStatement ps = conn.prepareStatement(sql);
				for (int i = 0; i < params.length; i++) {
					ps.setObject(1 + i, params[i]);
				}
				ResultSet rs = ps.executeQuery();

				// 结果集元数据(描述数据的数据)对象
				ResultSetMetaData md = rs.getMetaData();
				// md.getColumnCount(); //获取结果集的列数
				// md.getColumnName(column);//获取指定序号的列名
				if (rs.next() == false) {
					// 0行
					return null;
				} else {
					Map<String, Object> row = new HashMap<String, Object>();
					/**
					 * 取出该所有的列值，存放row集合中
					 */
					for (int i = 0; i < md.getColumnCount(); i++) {
						String columnName = md.getColumnName(i + 1);
						Object value = rs.getObject(i + 1);
						row.put(columnName, value);
					}
					if (rs.next()) {
						throw new SQLException("结果集的行数大于1");
					} else {
						return row;
					}
				}
			} finally {
				conn.close();
			}
		}
		
		
		
		
		
}


